﻿--获得本单位岗位异动人员数据
CREATE PROCEDURE [dbo].[proc_Employee_CompanyMove_GetList]
(
		@CompanyId nvarchar(400),
		@DepId nvarchar(4),
		@Stext nvarchar(50),
		@StartIndex nvarchar(20),
		@EndIndex nvarchar(20)
)
AS
DECLARE @sql NVARCHAR(4000)
DECLARE @swhere NVARCHAR(4000)
SET @swhere=''
IF(@CompanyId<>'0')
SET @swhere=@swhere+' EC.BCompanyId in('+@CompanyId+') and'
IF(@DepId<>'0')
SET @swhere=@swhere+' EC.BDepartmentId in(Select Id From department Where Left(bmbh,Len(@bmbh_T))=@bmbh_T) and'
IF(@Stext<>'') SET @swhere=@swhere+' EC.Ename like ''%'+@Stext+'%'' and'
SET @swhere=@swhere+' EC.BCompanyId is not null'
SET @sql=
		+' declare @bmbh_T  varchar(50) '
		+' Select @bmbh_T=bmbh From department Where id='+@DepId
		
		+' ;WITH list As(Select ROW_NUMBER() OVER (ORDER BY Id DESC)AS Row,'
		+' EC.Id,'
		+' (select ENumber from Employee E where EC.EId=E.Id) as ENumber,'
		+' (select ETempNumber from Employee E where EC.EId=E.Id) as ETempNumber,'
		+' EC.EId,'
		+' (select EName from Employee E where EC.EId=E.Id) as EName,'
		+' (select Sex from Employee E where EC.EId=E.Id) as Sex,'
		+' (select CompanyName from Company where id=ec.BCompanyId) as CompanyName,'
		+' /*(select DName from Department where id=ec.BDepartmentId)*/ BDepartmentname as DName,'
		+' (select PostName from Post P where id=(select PostId from employee E where EC.EId=E.Id)) as PostName,'
		+' (select Title from Grade G where id=(select GradeId from employee E where EC.EId=E.Id)) as GradeName,'
		+' (select CompanyName from Company where id=ec.ACompanyId) AS BCompanyName,'
		+' /*(select DName from Department where id=ec.ADepartmentId)*/ ADepartmentname as BDName,	'
		+' EC.Audit,'
		+' EC.AuditPerson,'
		+' EC.AuditTime,EC.Proposer,EC.ApplionTime,'
		+' (select EName from Employee E where EC.Proposer=E.UserName) as ProposerName,[BPid],[BCid],[APid],[ACid] ,[BZhiWu],[AZhiWu]'
		+' from Employee_CompanyMove  EC Where '
		+@swhere
		+' )' 
		+' Select  *,(SELECT COUNT(0) FROM list) AS coun,
			(Select p.Title From Province p  where p.Id=BPid) as Bpname,			
			(Select c.Title From city c  where c.Id=BCid) as Bcname,
			(Select p.Title From Province p  where p.Id=APid) as Apname,			
			(Select c.Title From city c  where c.Id=ACid) as Acname From list Where Row Between '+ @StartIndex+' and '+@EndIndex
		
		EXEC(@sql)

----获得本单位岗位异动人员数据
--ALTER proc [dbo].[proc_Employee_CompanyMove_GetList]
--(
--		@CompanyId int,
--		@DepId int,
--		@Stext nvarchar(50),
--		@StartIndex int,
--		@EndIndex int

--)
--as
--begin
--			declare @bmbh_T  varchar(50)

--Select @bmbh_T=bmbh From department Where id=@DepId
	
--	;WITH list As(Select ROW_NUMBER() OVER (ORDER BY Id DESC)AS Row,
--		EC.Id,
--			(select ENumber from Employee E where EC.EId=E.Id) as ENumber,
--			(select ETempNumber from Employee E where EC.EId=E.Id) as ETempNumber,
--			EC.EId,
--			(select EName from Employee E where EC.EId=E.Id) as EName,
--			(select Sex from Employee E where EC.EId=E.Id) as Sex,
--			(select CompanyName from Company where id=ec.BCompanyId) as CompanyName,
--			(select DName from Department where id=ec.BDepartmentId) as DName,
--			(select PostName from Post P where id=(select PostId from employee E where EC.EId=E.Id)) as PostName,
--			(select Title from Grade G where id=(select GradeId from employee E where EC.EId=E.Id)) as GradeName,
			
--			(select CompanyName from Company where id=ec.ACompanyId) AS BCompanyName,
--			(select DName from Department where id=ec.ADepartmentId) as BDName,			
--			EC.Audit,
--			EC.AuditPerson,
--			EC.AuditTime
--		from Employee_CompanyMove  EC
		
--		Where 
--		(@CompanyId=0 Or EC.BCompanyId=@CompanyId)
--		and (@DepId=0 Or  EC.BDepartmentId in(Select Id From department Where Left(bmbh,Len(@bmbh_T))=@bmbh_T))
--		and (@Stext='' Or EC.Ename like '%'+@Stext+'%')
--	)
	
--	Select  *,(SELECT COUNT(0) FROM list) AS coun From list Where Row Between @StartIndex and @EndIndex
--end
